﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UtilityClassLIB;

namespace AttributeGroupsClassLIB
{
    public class AttributesGroupClass
    {
        UtilityClass utility_obj = new UtilityClass();
        String connectionString = UtilityClass.ConnectionString();

        // gets list of attrbutes for a group. 
        public DataTable GetListofAttributesbyGroup(string groupselected)
        {
            DataTable dt = new DataTable();
            SqlConnection connection = new SqlConnection(connectionString);

            using (SqlCommand SelectCommand = new SqlCommand(
                "Select a.attributeID, b.attributeName from tbAttribgrp a INNER JOIN tbAttributes b " +
                "ON a.attributeID = b.attributeID where a.grpID = '"
                + groupselected + "'", connection))
            {
                connection.Open();
                dt.Load(SelectCommand.ExecuteReader());
                return dt;
            }

        }

        //add attribute to group
        public string InsertRowinAttributeGroupTable(string groupname, string attributename, int gaValue, string agd)
        {
            string result = "";
            SqlConnection connection = new SqlConnection(connectionString);
            int groupid = 0;
            int attributeid = 0;

            groupid = utility_obj.GetGroupIDfromName(groupname);
            attributeid = utility_obj.GetAttributeIDfromName(attributename);

            //get attributegroup table
            DataTable dt = utility_obj.fillDataTable("tbAttribgrp");
            DataRow[] row_result = dt.Select();


            foreach (DataRow row in dt.Rows)//check to see if user/group combo exists already
            {
                if (((Int32)row[0] == attributeid) && ((Int32)row[1] == groupid))
                {
                    result = "Attribute is already in group.";
                }
            }

            if (result == "")
            {
                try //inserts row into user table
                {

                    using (SqlCommand insertCommand = new SqlCommand(
                    "INSERT INTO tbAttribgrp VALUES(@attributeID, @grpID, @value, @grpAttribdisabled)", connection))
                    {
                        insertCommand.Parameters.AddWithValue("@attributeID", attributeid);
                        insertCommand.Parameters.AddWithValue("@grpID", groupid);
                        insertCommand.Parameters.AddWithValue("@value", gaValue);
                        insertCommand.Parameters.AddWithValue("@grpAttribdisabled", agd);

                        insertCommand.Connection = connection;
                        connection.Open();
                        insertCommand.ExecuteNonQuery();
                        result = " Success! " + attributename + " added to " + groupname + " group";
                    }
                }//end try
                catch (System.Data.SqlClient.SqlException sqlException)
                {
                    result = sqlException.Message;
                }
                catch (Exception e)
                {
                    result = "Opps! Something went wrong. " + e.Message;
                }

            }

            return result;
        }

        //-----------------------------for session class--------------------------------------------------------

        //gets user info for session class (could be in user class, I didn't do the user class, so I stuck it here)
        public string GetUser(int userid)
        {
            string results = "";
            SqlDataReader reader;

            try
            {
                using (
                SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand SelectCommand = new SqlCommand("SELECT * FROM tbUser WHERE userID = '" + userid + "'", connection);
                    SelectCommand.Connection.Open();
                    reader = SelectCommand.ExecuteReader();
                    while (reader.Read())
                    {
                        results = String.Format("Groups and Attributes for: UserID: {0} Name: {1} {2}", reader["userId"], reader["firstName"], reader["lastName"]);
                    }
                }
            }//end try
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                results = sqlException.Message;
            }
            catch (Exception e)
            {
                results = "Opps! Something went wrong. " + e.Message;
            }
            return results;
        }

        //gets all groups for user
        public DataTable GetGroups(int userid)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand SelectCommand = new SqlCommand(
                "SELECT  u.userID, ug.grpID, g.groupName, g.groupPriority, g.groupDescription " +

               "FROM tbUsergrp ug INNER JOIN tbGroups g ON g.groupID = ug.grpID " +

                "INNER JOIN tbUser u ON u.userID = ug.userID " +

               "WHERE ug.userID = '" + userid + "' and ug.userGroupDisabled = 'N'  ", connection);

            DataTable dt = new DataTable();

            connection.Open();
            dt.Load(SelectCommand.ExecuteReader());
            connection.Close();
            return dt;
        }
        //gets all attributes for user
        public DataTable GetAttributes(int userid)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand SelectCommand = new SqlCommand(
               "select ug.userID, g.groupName, g.groupPriority, ag.attributeID, a.attributeName " +

              " FROM tbAttributes a" +

              " INNER JOIN tbAttribgrp ag ON ag.attributeID = a.attributeID" +

              " INNER JOIN tbGroups g ON g.groupID = ag.grpID " +

              " INNER JOIN tbUsergrp ug  ON ug.grpID = ag.grpID" +

              " WHERE ug.userID = " + userid + " and ug.userGroupDisabled = 'N' and ag.grpAttribdisable ='N' and a.attributeDisabled ='N' ", connection);

            DataTable dt = new DataTable();

            connection.Open();
            dt.Load(SelectCommand.ExecuteReader());
            connection.Close();
            return dt;
        }

        //gets only attribute from highest level group
        public DataTable GetHighestRankedUserAttributes(int userid)
        {

            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand SelectCommand = new SqlCommand(
                //select the columns you need from the different tables. The letters before are the table alias
                "SELECT  ug.userID, ug.grpID, g.groupPriority, ag.attributeID, a.attributeName " +
                //which table to get columns, use intuitive aliases that 
                //you don't have to fight your brain to remember so give it the alias ug (had 20 min lecture from dh on alias naming)
               "FROM tbUsergrp ug  " +
               //joins groups to user groups only for records that match values in these columns
               "INNER JOIN tbGroups g ON g.groupID = ug.grpID " +
               //joins attributegroup only for records that match values in these columns
               "INNER JOIN tbAttribgrp ag ON ag.grpID = ug.grpID " +
               //joins attributes only for records that match values in these columns
               "INNER JOIN tbAttributes a ON a.attributeID = ag.attributeID " +
               //where the user equals user searched for in form, and neither group or attribute is disabled
               "WHERE ug.userID = '" + userid + "' and ug.userGroupDisabled = 'N' and ag.grpAttribdisable ='N' " +
               //this is the tricky part! need to get highest group priority. use subquery that joins to the main query
               // by the selected user. get all of their groups then GROUP BY on user to force one row per user.
               //then return only lowest priority from all groups that user has using min()
               //because for us 1 is the highest priority
                "and g.groupPriority in (select gpmin from (select ug1.userID,  min(g1.groupPriority) gpmin " +
                //from the groups the user is in:
                                        "from tbGroups g1, tbUsergrp ug1 " +
                                        //join subquery group to subquery usergroup
                                       "where g1.groupID = ug1.grpID " +
                                       //join subquery user to main query user
                                        " and ug1.userID = ug.userID " +
                                       //get back only one row per user (so we can then min() for priority)
                                       " group by ug1.userID " +
                //v is the alias for the subquery. thank you sql server!  we can't have nameless queries...
                                        " )  v )", connection);
            DataTable dt = new DataTable();

            connection.Open();
            dt.Load(SelectCommand.ExecuteReader());
            connection.Close();
            return dt;
        }

        

       
    }//end class
}//end namespace
